package cvs;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class UploadCSVToTestServer {

	public static void main(String[] args) {

		System.out.println("start");
		uploadProduct();

		System.out.println("end");
	}

	private static void uploadProduct() {
		String tableName = "temp_product_upload";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\epos3\\ced\\product\\2010-06-21 CED Product Hierarchy.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void showCreateTable(File newFile, String table) {
		FileReader reader = null;
		try {
			reader = new FileReader(newFile);

			DelimitedFileReader csvReader = new DelimitedFileReader(reader);
			String[] tokens = csvReader.readNext();

			String sql = "CREATE TABLE " + table + " ( ";
			for (int i = 0; i < tokens.length; i++) {
				sql = sql + tokens[i] + " varchar(255)";

				if (i < tokens.length - 1)
					sql = sql + ',';
			}
			sql = sql + " )";
			System.out.println(sql);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null)
				try {
					reader.close();
				} catch (IOException e) {
				}
		}
	}

	public static void loadFile(File newFile, String table) {
		FileReader reader = null;
		try {
			reader = new FileReader(newFile);

			DelimitedFileReader csvReader = new DelimitedFileReader(reader);
			String[] tokens = csvReader.readNext();

			String sql = "insert into " + table + " ( ";
			for (int i = 0; i < tokens.length; i++) {
				sql = sql + tokens[i];

				if (i < tokens.length - 1)
					sql = sql + ',';
			}

			sql = sql + ") values( ";

			for (int i = 0; i < tokens.length; i++) {
				sql = sql + "?";

				if (i < tokens.length - 1)
					sql = sql + ',';
			}
			sql = sql + ");";

			System.out.println(sql);

			PreparedStatement ps;
			ResultSet rs;

			if (1 == 1) {
				System.out.println(sql);
				System.out.println();
				System.out.println();
				System.out.println();
			}
			DBConnectorTest connector = new DBConnectorTest();

			try {
				connector.start();
				Connection connection = connector.getConnection();

				ps = connection.prepareStatement(sql);

				while ((tokens = csvReader.readNext()) != null) {

					for (int i = 0; i < tokens.length; i++) {
						ps.setString(i + 1, tokens[i]);
					}
					ps.executeUpdate();
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				connector.close();
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null)
				try {
					reader.close();
				} catch (IOException e) {
				}
		}
	}
}
